﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using DB.SqlHelper;
using Framework.Common.Common;
using Mode;

namespace Framework.DataAccess
{
    public class examinationDA
    {
        private DbInstance db;

        /// <summary>
        /// 创建考场
        /// </summary>
        /// <param name="examData"></param>
        /// <returns></returns>
        public int AddExam(examination_table examData)
        {
            DbManager.BeginTransaction();
            db = DbManager.GetDbInstance();
            try
            {
                string strSql = "Insert Into examination_table(name,model,[date],[begin],[end],[time],pass,[status],has_QA)" +
                    "Values(@name,{0},'{1}','{2}','{3}',{4},{5},0,(Select has_QA from model_table where id={0}));Select Scope_Identity()As examID;";
                strSql = string.Format(strSql, examData.model, examData.date.ToString("yyyy-MM-dd HH:mm"), examData.begin.ToString("yyyy-MM-dd HH:mm"),
                    examData.end.ToString("yyyy-MM-dd HH:mm"), examData.time, examData.pass);
                DbCommand childCmd = db.GetSqlStringCommand(strSql);
                db.AddInParameter(childCmd, "@name", DbType.String, examData.name);
                int examID = db.ExecuteScalar(childCmd).ToInt(0);
                if (examID == 0)
                {
                    DbManager.Rollback();
                    return examID;
                }

                //插入考生
                strSql = "Insert Into candidate_table(member,examination,score,[time],[status])" +
                    "Select M_ID,{0},0,0,-2 From member_view Where M_Status=0 and J_ID in (Select areRoleID From are_role_model_view Where M_ID={1})";
                strSql = string.Format(strSql, examID, examData.model);
                db.ExecuteNonQueryBySql(strSql);
                //提交
                DbManager.Commit();
                return examID;
            }
            catch (Exception e)
            {
                DbManager.Rollback();
                throw e;
            }
        }



        /// <summary>
        /// 获取考场列表
        /// </summary>
        /// <param name="pageInfo"></param>
        /// <param name="areID"></param>
        /// <param name="modelID"></param>
        /// <param name="beginDate"></param>
        /// <param name="endDate"></param>
        /// <param name="name"></param>
        /// <returns></returns>
        public PageList<object> GetExamList(PageView pageInfo, int areID, int modelID, int status, string name = "", DateTime? beginDate = null, DateTime? endDate = null)
        {
            string strWhere = " Where 1=1";
            strWhere += (areID > -1 ? string.Format(" And model in(Select M_ID From are_role_model_view Where A_ID={0})", areID) : "");
            strWhere += (modelID > -1 ? " And model=" + modelID : "");
            strWhere += (status > -1 ? " And E_Status=" + status : "");
            strWhere += (name.Length > 0 ? string.Format(" And E_Name Like '%{0}%'", name.Replace("'", "''")) : "");
            strWhere += (beginDate.HasValue ? string.Format(" And E_BeginDate>='{0}'", beginDate.Value.AddDays(-1).ToString("yyyy-MM-dd")) : "");
            strWhere += (endDate.HasValue ? string.Format(" And E_EndDate<='{0}'", endDate.Value.AddDays(1).ToString("yyyy-MM-dd")) : "");

            PageList<object> list = new PageList<object>();
            db = DbManager.GetDbInstance();
            string strSql = "SELECT  COUNT(*) as Row FROM  examination_view " + strWhere;
            int rowCount = db.ExecuteScalarBySql(strSql).ToInt(0);


            string orderby = " Order By [E_BeginDate] Desc ";
            if (pageInfo.SortName != "")
                orderby = string.Format(" Order By {0} {1}", pageInfo.SortName, pageInfo.SortOrder);
            strSql = "SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) AS rows,{1},CONVERT(varchar(16),[E_BeginDate],120)As BeginStr, CONVERT(varchar(16),[E_EndDate],120)As EndStr," +
                //"(Select COUNT(*)From member_view Where M_Status=0 and J_ID in (Select areRoleID From are_role_model_view Where M_ID=A.model))As YKRS," +
                "(Select COUNT(*) From candidate_table Where examination=A.E_ID)As YKRS," +
                "(Select COUNT(*) From candidate_table Where examination=A.E_ID And [status]>-2)As SkRS From {2} As A {3})As T0 Where rows Between {4} And {5}";
            strSql = string.Format(strSql, orderby, pageInfo.Fields, "examination_view", strWhere, ((pageInfo.PageIndex - 1) * pageInfo.PageSize + 1), pageInfo.PageIndex * pageInfo.PageSize);

            DbCommand cmd = db.GetSqlStringCommand(strSql);
            DataTable dt = db.ExecuteDataTable(cmd);
            dt.Columns.Add("rolename", typeof(string));
            ModeAdpater myModel = new ModeAdpater();
            for (int index = 0; index < dt.Rows.Count; index++)
            {
                int model = dt.Rows[index]["model"].ToInt();
                dt.Rows[index]["rolename"] = myModel.GetModelRoleName(model);
            }

            list.Total = rowCount;
            list.Data = dt;

            return list;
        }

        /// <summary>
        /// 修改考场的停用/启用状态
        /// </summary>
        /// <param name="topicID"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public bool SetExamStatus(int examID, int status)
        {
            db = DbManager.GetDbInstance();
            string strSql = string.Format("Update examination_table Set status={0} Where [id]={1}", status, examID);
            int count = db.ExecuteNonQueryBySql(strSql);
            return count == 1;
        }

        /// <summary>
        /// 根据考场ID获取考生列表
        /// </summary>
        /// <param name="pageInfo"></param>
        /// <param name="examID"></param>
        /// <returns></returns>
        public PageList<object> GetCandidateList(PageView pageInfo, int examID)
        {
            db = DbManager.GetDbInstance();
            string strWhere = " Where ExamID=" + examID;

            PageList<object> list = new PageList<object>();

            string strSql = "SELECT  count(*) as Row FROM  candidate_view " + strWhere;
            int rowCount = db.ExecuteScalarBySql(strSql).ToInt(0);

            string orderby = " Order By  C_ID ";
            if (pageInfo.SortName != "")
                orderby = string.Format(" Order By {0} {1}", pageInfo.SortName, pageInfo.SortOrder);
            strSql = "SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) AS rows,{1} From {2} {3})As T0 Where  rows Between {4} And {5}";

            strSql = string.Format(strSql, orderby, "*", "candidate_view", strWhere, ((pageInfo.PageIndex - 1) * pageInfo.PageSize + 1), pageInfo.PageIndex * pageInfo.PageSize);

            DbCommand cmd = db.GetSqlStringCommand(strSql);
            DataTable dt = db.ExecuteDataTable(cmd);


            list.Total = rowCount;
            list.Data = dt;

            return list;
        }
        /// <summary>
        /// 根据考生ID获取考场列表
        /// </summary>
        /// <param name="pageInfo"></param>
        /// <param name="examID"></param>
        /// <returns></returns>
        public PageList<object> GetCandidateList2(PageView pageInfo, int mebeid, DateTime? beginDate = null, DateTime? endDate = null)
        {
            db = DbManager.GetDbInstance();
            string strWhere = " Where C_Status > -2 and  MemberID=" + mebeid;

            if (beginDate.HasValue)
                strWhere += string.Format(" And [C_Date]>='{0}'", beginDate.Value.ToString("yyyy-MM-dd"));
            if (endDate.HasValue)
                strWhere += string.Format(" And [C_Date]<='{0} 23:59'", endDate.Value.ToString("yyyy-MM-dd"));

            PageList<object> list = new PageList<object>();

            string strSql = "SELECT  count(*) as Row FROM  candidate_view " + strWhere;
            int rowCount = db.ExecuteScalarBySql(strSql).ToInt(0);

            string orderby = " Order By  C_ID ";
            if (pageInfo.SortName != "")
                orderby = string.Format(" Order By {0} {1}", pageInfo.SortName, pageInfo.SortOrder);
            strSql = "SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) AS rows,{1} From {2} {3})As T0 Where  rows Between {4} And {5}";

            strSql = string.Format(strSql, orderby, "*", "candidate_view", strWhere, ((pageInfo.PageIndex - 1) * pageInfo.PageSize + 1), pageInfo.PageIndex * pageInfo.PageSize);

            DbCommand cmd = db.GetSqlStringCommand(strSql);
            DataTable dt = db.ExecuteDataTable(cmd);


            list.Total = rowCount;
            list.Data = dt;

            return list;
        }
        /// <summary>
        /// 获得考场
        /// </summary>
        /// <param name="j_id"></param>
        /// <param name="pageInfo"></param>
        /// <returns></returns>
        public PageList<object> getexaminationList(int j_id, int m_id, PageView pageInfo)
        {
            string strWhere = " where  [status]=0 and areRoleID=" + j_id;
            strWhere += " and   CONVERT(varchar(16), getdate(),121)  between [begin] and  [end]  and  id not in(select examination from candidate_table where member=" + m_id + " and [status] > -2)";

            PageList<object> list = new PageList<object>();

            string strSql = "select count(*) as Row from (select a.*,c.areRoleID from examination_table as a inner join model_table as b on a.model=b.id inner join model_are_role as c on b.id=c.modelID )as A ";
            strSql += strWhere;



            db = DbManager.GetDbInstance();

            int rowCount = db.ExecuteScalarBySql(strSql).ToInt(0);


            string orderby = " Order By  id ";
            if (pageInfo.SortName != "")
                orderby = string.Format(" Order By {0} {1}", pageInfo.SortName, pageInfo.SortOrder);
            strSql = "SELECT * FROM (SELECT ROW_NUMBER() OVER ({0}) AS rows,{1} From (select a.*,c.areRoleID from examination_table as a inner join model_table as b on a.model=b.id inner join model_are_role as c on b.id=c.modelID )as A {2})As T0 Where  rows Between {3} And {4}";


            strSql = string.Format(strSql, orderby, "A.*", strWhere, ((pageInfo.PageIndex - 1) * pageInfo.PageSize + 1), pageInfo.PageIndex * pageInfo.PageSize);


            DbCommand cmd = db.GetSqlStringCommand(strSql);
            DataTable dt = db.ExecuteDataTable(cmd);


            list.Total = rowCount;
            list.Data = dt;

            return list;
        }

        /// <summary>
        /// 问答题阅卷
        /// </summary>
        /// <param name="c_id">考生ID</param>
        /// <param name="sheetList">答题id及得分</param>
        public void ScoreQATopic(int c_id, List<sheetReplay_score> sheetList)
        {
            string strSql = "";
            foreach (sheetReplay_score replayData in sheetList)
            {
                strSql += string.Format("Update sheet_table Set score={0} Where id={1};\r\n", replayData.score, replayData.sheetID);
            }
            strSql += "SELECT sum([score]) AS score, sum([full]) AS [full] from sheet_table WHERE candidate ={0};\r\n" +
                "Select pass From examination_table Where id=(Select examination From candidate_table Where id={0})";
            strSql = string.Format(strSql, c_id);
            DbManager.BeginTransaction();
            DbInstance myDB = DbManager.GetDbInstance();
            try
            {
                DataSet dst = myDB.ExecuteDataSetBySql(strSql);
                if (dst.Tables.Count == 2)
                {
                    int score = dst.Tables[0].Rows[0]["score"].ToInt();
                    int fullScore = dst.Tables[0].Rows[0]["full"].ToInt();
                    int pass = dst.Tables[1].Rows[0]["pass"].ToInt();
                    int passScore = (fullScore * pass / 100).ToInt();
                    strSql = string.Format("Update candidate_table set score={0},status={1} Where id={2};", score, score < passScore ? "1" : "2", c_id);
                    myDB.ExecuteNonQueryBySql(strSql);
                }
                else
                    throw new Exception("评分失败！");
                DbManager.Commit();
            }
            catch
            {
                DbManager.Rollback();
            }
        }



        public PageList<object> GetUserExamList(int userid, int page, int pagesize, string storname, string stororder)
        {
            PageList<object> list = new PageList<object>();
            list.Total = 0;
            list.Data = null;

            DbInstance tdb = DbManager.GetDbInstance();
            StringBuilder sb = new StringBuilder();


            sb.Append("select count(*) as count ");
            sb.Append("from candidate_table ");
            sb.Append("inner join examination_table ");
            sb.Append("on examination_table.id = candidate_table.examination ");
            sb.Append("where candidate_table.member = " + userid);

            list.Total = tdb.ExecuteScalarBySql(sb.ToString()).ToInt();
            sb.Clear();

            sb.Append("SELECT * FROM ( ");
            sb.Append("SELECT ROW_NUMBER() OVER(ORDER BY  C_Date DESC) AS row, ");
            sb.Append("ExamID as examid, ");
            sb.Append("C_ID as candiid, ");
            sb.Append("ExamName as examname, ");
            sb.Append("ModelScore as total, ");
            sb.Append("C_Score as candisrouce, ");
            sb.Append("cast(ExamPass * ModelScore /100.00 as decimal(18,2)) as passsce, ");
            sb.Append("ExamTime as examtime, ");
            sb.Append("C_Time as canditime, ");
            sb.Append("C_Date as candidate, ");
            sb.Append("C_Status as candistate ");
            sb.Append("from candidate_view where MemberID=67 ");
            sb.Append(") AS TMPTABLE ");
            sb.Append("WHERE row Between " + ((page - 1) * pagesize + 1) + " and " + page * pagesize);
            sb.Append("order by " + storname + " " + stororder);

            list.Data = tdb.ExecuteDataTableBySql(sb.ToString());

            return list;
        }

        public int ReExamRoom(int candidateid, int userid)
        {
            int result = 0;

            DbManager.BeginTransaction();
            DbInstance myDB = DbManager.GetDbInstance();
            try
            {
                DataTable tdt = myDB.ExecuteDataTableBySql("select id from sheet_table where candidate =" + candidateid);
                if (tdt.Rows.Count > 0)
                {

                    StringBuilder sb = new StringBuilder();
                    foreach (DataRow row in tdt.Rows)
                    {
                        sb.Append(row["id"] + ",");
                    }
                    string ids = sb.ToString().Trim(',');

                    myDB.ExecuteNonQueryBySql("delete reply_table where sheet in(" + ids + ")");
                    myDB.ExecuteNonQueryBySql("delete sheet_table where candidate=" + candidateid);
                }

                myDB.ExecuteNonQueryBySql("update candidate_table set score=0, [time]=0, [date]=NULL, status=-2 where id=" + candidateid);

                DbManager.Commit();
                result = 1;
            }
            catch (Exception e)
            {
                DbManager.Rollback();
            }

            return result;
        }


        public int DeleteExamRoom(int examid)
        {
            int result = 0;

            DbManager.BeginTransaction();
            DbInstance myDB = DbManager.GetDbInstance();

            try
            {
                string sql = "select id from candidate_table where examination=" + examid;
                DataTable dt = myDB.ExecuteDataTableBySql(sql);
                if (dt.Rows.Count > 0)
                {
                    StringBuilder sb = new StringBuilder();
                    foreach (DataRow row in dt.Rows)
                    {
                        sb.Append(row["id"] + ",");
                    }
                    string canids = sb.ToString().TrimEnd(',');
                    sb.Clear(); dt.Clear();


                    sql = "select * from sheet_table where candidate in (" + canids + ")";
                    dt = myDB.ExecuteDataTableBySql(sql);
                    if (dt.Rows.Count > 0)
                    {
                        foreach (DataRow row in dt.Rows)
                        {
                            sb.Append(row["id"] + ",");
                        }
                        string sheetid = sb.ToString().TrimEnd(',');
                        sb.Clear(); dt.Clear();

                        sql = "delete reply_table where sheet in (" + sheetid + ")";
                        myDB.ExecuteNonQueryBySql(sql);
                    }

                    sql = "delete sheet_table where candidate in(" + canids + ")";
                    myDB.ExecuteNonQueryBySql(sql);


                    sql = "delete candidate_table where examination=" + examid;
                    myDB.ExecuteNonQueryBySql(sql);
                }

                sql = "delete examination_table where id=" + examid;
                myDB.ExecuteNonQueryBySql(sql);

                DbManager.Commit();
                result = 1;
            }
            catch (Exception)
            {
                DbManager.Rollback();
            }


            return result;
        }

        public ExamChartData ExamChart(int examid)
        {
            db = DbManager.GetDbInstance();

            DataTable dt = db.ExecuteDataTableBySql("select MemberName as name, C_Score as srouce from candidate_view where ExamID =" + examid);
            ExamChartData result = new ExamChartData(dt);
            return result;

        }


        public UserExamChart UserExamChart(int userid)
        {
            db = DbManager.GetDbInstance();

            string sql = "select ExamName as examname, C_Score as score,  " +
                     "cast(ExamPass * ModelScore /100.00 as decimal(18,2)) as passscore  " +
                     "from candidate_view where MemberID=" + userid;

            DataTable dt = db.ExecuteDataTableBySql(sql);
            UserExamChart result = new UserExamChart(dt);
            return result;
        }

    }
}
